package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.RecordCourse;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.Student;
import com.xmy.cultivate.entity.views.EnrollGrantCount;
import com.xmy.cultivate.entity.views.RecordCourseTiny;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 * 记止课 Mapper 接口
 * </p>
 *
 * @author baomidou
 * @since 2022-08-10
 */
public interface RecordCourseMapper extends BaseMapper<RecordCourse> {


    @Select("select * from record_course where scheduling_detail_id = #{schedulingId} and student_id = #{studentId} limit 0,1")
    public abstract RecordCourseTiny getListForSchedulingIdAndStuId(@Param("schedulingId") Long schedulingId,@Param("studentId") Long studentId);

    /**
     * 请假未补的数量
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @param subjectsId
     * @param lessonType
     * @return
     */
    @Select("select count(id) as count from record_course where student_id = ${studentId} and deleted = 0 and year_part = ${yearPart} and `quarter` = ${quarterNum} and lesson_type =${lessonType} and subjects_id =${subjectsId} and `status` =2 and is_repair<>1")
    public abstract Integer getLackCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);


    /**
     * 获取到课
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @return
     */
    @Select("select count(id) as count from record_course where deleted=0 and year_part = ${yearPart} and `quarter` = ${quarterNum} and lesson_type = 1 and `status`=1 and student_id = ${studentId} and subjects_id = ${subjectsId} and is_deduct_house =1 and is_deduct_money =1")
    public abstract Integer getRecordCountArrive(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId);


    @Select({"<script>",
            "SELECT",
            "SUM( use_leave_num ) AS use_leave_num ",
            "FROM",
            "(",
            "SELECT",
            "IFNULL( SUM( use_leave_num ), 0 ) use_leave_num ",
            "FROM",
            "record_course ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND student_id = ${studentId} ",
            "AND lesson_type = ${lessonType} ",
            "AND subjects_id = ${subjectsId} ",
            "AND deleted = 0",
            ") AS t",
            "</script>"})
    public abstract Integer getUseLeaveCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);

    @Select({"<script>",
            "SELECT",
            "* ",
            "FROM",
            "record_course ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND `status` = 2 ",
            "AND student_id = ${studentId} ",
            "AND subjects_id = ${subjectsId} ",
            "AND year_class_id = ${yearClassId} ",
            "AND is_repair &lt;&gt;1 ",
            "AND is_deduct_money = 0",
            "ORDER BY school_date asc",
            "limit 1",
            "</script>"})
    public abstract RecordCourse getLeaveData(@Param("yearPart") Integer yearPart,
                                              @Param("quarterNum") Integer quarterNum,
                                              @Param("lessonType") Integer lessonType,
                                              @Param("studentId") Long studentId,
                                              @Param("subjectsId") Long subjectsId,
                                              @Param("yearClassId") Integer yearClassId
    );

    @Select({"<script>",
            "SELECT",
            "count(id) as count",
            "FROM",
            "record_course",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND `status` = 2 ",
            "AND student_id = ${studentId} ",
            "AND subjects_id = ${subjectsId} ",
            "AND is_repair &lt;&gt;1 ",
            "AND is_deduct_money = 0",
            "</script>"})
    public abstract Integer getLeaveCount(@Param("yearPart") Integer yearPart,
                                              @Param("quarterNum") Integer quarterNum,
                                              @Param("lessonType") Integer lessonType,
                                              @Param("studentId") Long studentId,
                                              @Param("subjectsId") Long subjectsId);

    @Select({"<script>",
            "SELECT",
                    "* ",
                    "FROM",
                    "(",
                    "SELECT",
                      "t1.school_id,",
                      "t1.student_id,",
                      "t1.subjects_id,",
                      "t1.year_part,",
                      "t1.`quarter`,",
                      "t1.`lesson_type`,",
                      "t1.year_and_quarter,",
                      "count( t1.id ) AS solidCount ",
                    "FROM",
                      "record_course AS t1",
                      "INNER JOIN grade AS t2 ON t1.old_grade_id = t2.id ",
                      "AND t2.is_makeup = 0 ",
                    "WHERE",
                      "t1.deleted = 0 ",
                      "AND t1.year_part = ${yearPart} ",
                      "AND t1.`quarter` = ${quarterNum} ",
                      "AND t1.lesson_type = ${lessonType} ",
                      "AND t1.buckle_class_house &gt; 0 ",
                      "and (t1.`status`= 1 or (t1.`status` &lt;&gt; 1 and deduct_type = 4))",
                    "GROUP BY",
                      "t1.student_id,",
                      "t1.subjects_id ",
                    ") AS t ",
                    "WHERE",
                    "solidCount &gt;= ${rCount}",
            "</script>"})
    public abstract List<RecordCourse> getRecordCourseCount(@Param("yearPart") Integer yearPart,
                                                      @Param("quarterNum") Integer quarterNum,
                                                      @Param("lessonType") Integer lessonType,
                                                      @Param("rCount") Integer rCount);

    @Select("SELECT COUNT(id) FROM record_course WHERE deleted = 0 AND course_type = 8 AND `quarter` = #{quarter} AND year_part = #{yearPart} AND student_id = #{studentId} AND school_date =#{schoolDate} ")
    public abstract Integer getRecordCourseCountByData(@Param("quarter") Integer quarter, @Param("yearPart") Integer yearPart, @Param("studentId") Long studentId, @Param("schoolDate") String schoolDate);


    /**
     * 获取提成发放校区使用
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param studentId
     * @param lessonType
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "id,",
            "school_id,",
            "count( id ) AS count ",
            "FROM",
            "(",
            "SELECT",
            "* ",
            "FROM",
            "record_course ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND subjects_id = ${subjectsId} ",
            "AND student_id = ${studentId} ",
            "AND lesson_type = ${lessonType} ",
            "AND deleted = 0 ",
            "AND `status` = 1 ",
            "ORDER BY",
            "created_at ASC ",
            "LIMIT 2 ",
            ") AS t ",
            "GROUP BY",
            "school_id",
            "</script>"})
    public abstract List<EnrollGrantCount> getEnrollGrantCount(@Param("yearPart") Integer yearPart,
                                                               @Param("quarterNum") Integer quarterNum,
                                                               @Param("subjectsId") Long subjectsId,
                                                               @Param("studentId") Long studentId,
                                                               @Param("lessonType") Integer lessonType
    );


    /**
     * 获取超时未补的
     * @return
     */
    @Select({"<script>",
            "SELECT",
                      "*",
                    "FROM",
                      "(",
                      "SELECT",
                        "*,",
                        "DATE_ADD( school_date, INTERVAL ( 8 - DAYOFWEEK( school_date )) % 7+7 DAY ) AS next_sunday ",
                      "FROM",
                        "record_course ",
                      "WHERE",
                        "year_part = ${yearPart} ",
                        "AND `quarter` = ${quarterNum} ",
                        "AND lesson_type = ${lessonType} ",
                        "AND `status` = 2 ",
                        "AND is_repair &lt;&gt; 1 ",
                        "AND is_deduct_house = 1",
                        "AND is_deduct_money = 0 ",
                        "AND deleted = 0 ",
                        "AND school_date &gt;= '2024-11-25' ",
                      ") AS t ",
                    "WHERE",
                      "next_sunday &lt; #{nextSunday}",
            "</script>"})
    public abstract List<RecordCourse> getTimeoutNotRepair(@Param("yearPart") Integer yearPart,
                                                           @Param("quarterNum") Integer quarterNum,
                                                           @Param("lessonType") Integer lessonType,
                                                           @Param("nextSunday") String nextSunday);


    @Select({"<script>",
            "select count(id) AS count  from (SELECT",
                    "t1.*",
                    "FROM",
                    "record_course AS t1",
                    "LEFT JOIN cha_study_plan AS t2 ON t1.student_id = t2.student_id ",
                    "AND t1.year_part = t2.year_part ",
                    "AND t1.`quarter` = t2.quarter_num ",
                    "AND t1.lesson_num = t2.lesson_num ",
                    "AND t2.lesson_type = ${lessonType} ",
                    "AND t2.subjects_id = ${subjectsId} ",
                    "AND t2.status = 1 ",
                    "and t2.category_id = 1",
                    "AND date( t2.created_at ) &lt; #{nowLocalDate}",
                    "WHERE",
                    "t1.year_part = ${yearPart} ",
                    "AND t1.`quarter` = ${quarterNum} ",
                    "AND t1.`lesson_num` &lt;= ${maxTaskLesson} ",
                    "AND t1.lesson_type = 1 ",
                    "AND ((t1.`status` = 1) or (t1.`status` = 2 and t1.is_deduct_house = 1 and t1.is_deduct_money =1 and t1.is_repair &lt;&gt; 1)) ",
                    "AND t1.`end_date_time` &lt;= #{startDateTime} ",
                    "AND t1.subjects_id = ${subjectsId} ",
                    "AND t1.deleted = 0 ",
                    "AND t2.id IS NULL ",
                    "and t1.student_id = ${studentId}",
                    "GROUP BY",
                    "t1.student_id) as t",
            "</script>"})
    public Integer getNotPassCount(@Param("yearPart") Integer yearPart,
                                   @Param("quarterNum") Integer quarterNum,
                                   @Param("studentId") Long studentId,
                                   @Param("nowLocalDate") String nowLocalDate,
                                   @Param("startDateTime") String startDateTime,
                                   @Param("subjectsId") Long subjectsId,
                                   @Param("lessonType") Integer lessonType,
                                   @Param("maxTaskLesson") Integer maxTaskLesson
                                   );

    @Select({"<script>",
            "SELECT",
            "count( id ) AS count ",
            "FROM",
            "(",
            "SELECT",
            "t1.id,",
            "t3.student_id,",
            "count( * ) AS all_count,",
            "SUM( CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END ) AS pass_count ",
            "FROM",
            "( SELECT * FROM math_db.edu_task WHERE year_part = ${yearPart} AND `quarter_num` = ${quarterNum} AND task_course_num &lt;= ${tastMaxLessonNum} GROUP BY year_class_id, task_course_num ) AS t1",
            "INNER JOIN math_db.per_student_account AS t3 ON t1.year_part = t3.year_part ",
            "AND t1.quarter_num = t3.quarter_num ",
            "AND t1.year_class_id = t3.year_class_id",
            "LEFT JOIN cha_study_plan AS t2 ON t3.year_part = t2.year_part ",
            "AND t3.`quarter_num` = t2.quarter_num ",
            "AND t3.student_id = t2.student_id ",
            "AND t2.lesson_type = 3 ",
            "AND t1.task_course_num = t2.lesson_num ",
            "AND ( t2.subjects_id IN ( ${subjectsId} )) ",
            "AND t2.created_at &lt; #{nowLocalDate} ",
            "AND t2.category_id = 1 ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.`quarter_num` = ${quarterNum} ",
            "and t3.student_id = ${studentId}",
            "GROUP BY",
            "t3.student_id ",
            "HAVING",
            "( all_count &gt; pass_count )) AS t",
            "</script>"})
    public Integer getNotPassCountMath(@Param("yearPart") Integer yearPart,
                                       @Param("quarterNum") Integer quarterNum,
                                       @Param("subjectsId") Long subjectsId,
                                       @Param("studentId") Long studentId,
                                       @Param("yearClassId") Integer yearClassId,
                                       @Param("classTypeId") Long classTypeId,
                                       @Param("nowLocalDate") String nowLocalDate,
                                       @Param("tastMaxLessonNum") Integer tastMaxLessonNum);
}
